﻿using System;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Text;

using NHibernate;
using BEGDC.Utility.NHibernate;
using BEGDC.Utility.Castle;

using Castle.Services.Transaction;

namespace BEGDC.MIS.Office
{
    public abstract class OfficeService : IOffice
    {
        protected ISessionFactory Factory;

        protected abstract string TableName { get;}

        protected abstract string ServiceName { get;}

        public abstract DataSet GetDataFromFile(string fileName);

        public virtual DataSet GetDataFromDB(string strCondition)
        {
            DataSet result;
            using (ISession session = Factory.OpenSession())
            {
                string sql = "Select * From " + TableName + " Where " + strCondition;                
                result = new DataSet();
                DAO.FillDataSet(session, sql, result);
            }
            return result;
        }

        public abstract void SaveDataToFile(DataSet saveData, string fileName);

        [Transaction(TransactionMode.Requires)]
        public virtual void SaveDataToDB(DataSet saveData)
        {
            DataTable table;
            string table_name, columns, values, condition, sql_insert, sql_delete, sql_update;

            using (ISession session = Factory.OpenSession())
            {
                for (int i = 0; i < saveData.Tables.Count; i++)
                {
                    table = saveData.Tables[i];
                    table_name = table.TableName;
                    condition = string.Empty;
                    sql_delete = "delete from " + table_name + " where " + table_name + "ID in (select B." + table_name
                    + "ID from " + table_name + " B inner join (select * from " + table_name + " where isNew = 1) A on ";

                    for (int j = 0; j < table.Rows.Count; j++)
                    {
                        columns = string.Empty;
                        values = string.Empty;
                        DataRow row = table.Rows[j];
                        sql_insert = "insert " + table_name + "(";

                        if (j == 0)
                        {
                            foreach (DataColumn col in table.Columns)
                            {
                                if (col.ExtendedProperties.ContainsKey("Key"))
                                {
                                    condition += " A." + col.ColumnName + "=B." + col.ColumnName + " and";
                                }
                            }
                            if (condition != string.Empty)
                                condition = condition.Substring(0, condition.Length - 3);
                        }

                        foreach (DataColumn col in table.Columns)
                        {
                            columns += col.ColumnName + ",";
                            if (col.ExtendedProperties.ContainsKey("FixedValue"))
                            {
                                if (col.ExtendedProperties.ContainsKey("Convert"))
                                    values += "'" + CommonUtility.ConvertDataByColumn(col.ColumnName, col.ExtendedProperties["FixedValue"].ToString(), session) + "',";
                                else
                                    values += "'" + col.ExtendedProperties["FixedValue"].ToString() + "',";
                            }
                            else
                            {
                                if (col.ExtendedProperties.ContainsKey("Convert"))
                                    values += "'" + CommonUtility.ConvertDataByColumn(col.ColumnName, row[col.ColumnName].ToString(), session) + "',";
                                else
                                    values += "'" + CommonUtility.ConvertQuarter(col.ColumnName, row[col.ColumnName].ToString(), row) + "',";
                            }
                            
                        }

                        sql_insert += columns + "isNew) values(" + values + "'1')";
                        DAO.ExecuteNonQuery(session, sql_insert);
                    }

                    sql_delete += condition + "where B.isNew =0)";
                    DAO.ExecuteNonQuery(session, sql_delete);

                    sql_update = "update " + table_name + " set isNew =0 where isNew=1";
                    DAO.ExecuteNonQuery(session, sql_update);
                }
            }
        }

        
    }

    
}
